﻿using MvcLibrary.Repository;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using WebVideo.Areas.Admin.Models;
using WebVideo.Models;
using CarlosAg.ExcelXmlWriter;
namespace WebVideo.Controllers
{
    public class OrganizationController : Controller
    {
        //
        // GET: /Organization/
        #region "Info user"
        public ActionResult InfoUser()
        {
            dbVideoDataContext db = new dbVideoDataContext ();
            EntityBusiness b = db.EntityBusinesses.Where(g=>g.UserName == Request.Cookies["UserId"].Value).First();
            return View(b);
        }

        public string ChangeInfo(string CompanyName, string Email, string Address, string PhoneNumber, string Fax)
        {
            try
            {
                dbVideoDataContext db = new dbVideoDataContext();
                EntityBusiness b = db.EntityBusinesses.Where(k => k.UserName == Request.Cookies["UserId"].Value).First();

                b.CompanyName = CompanyName;
                b.Email = Email;
                b.Address = Address;
                b.PhoneNumber = PhoneNumber;
                b.Fax = Fax;
                db.SubmitChanges();
                return ("Complete");
            }
            catch
            {
                return ("Error");
            }
           
        }
        public string ChangePassWord(string PassWord, string NewPass)
        {
            dbVideoDataContext db = new dbVideoDataContext();
            Security ser = new Security();
            PassWord = ser.GetHashPassword(PassWord);
            try
            {
                var query = db.EntityBusinesses.Where(k => k.UserName == Request.Cookies["UserId"].Value  && k.Pass == PassWord);
                if (query != null)
                {
                    EntityBusiness p = query.First();
                    NewPass = ser.GetHashPassword(NewPass);
                    p.Pass = NewPass;
                }
                db.SubmitChanges();
                return ("Complete");
            }
            catch
            {
                return ("Error");
            }
        }
        [ValidateInput(false)]
        [HttpPost]
        public ActionResult ChangeAvatar(HttpPostedFileBase Image)
        {
            dbVideoDataContext db = new dbVideoDataContext();
            try
            {
                if (Image != null && Image.ContentLength > 0)
                {
                    int kb = Image.ContentLength / 1024; //file size kb
                    if (kb >= 2048) // file qua lon
                    {
                        return RedirectToAction("InfoUser", "Organization");
                    }

                    ImageHelper imgHelper = new ImageHelper();
                    string encodestring = imgHelper.encodeImageFile(Image);

                    if (encodestring == "!")
                        return RedirectToAction("InfoUser", "Organization");

                    var path = Path.Combine(Server.MapPath("~/Content/Images/Avatar"), encodestring);
                    Image.SaveAs(path);
                    var query = db.EntityBusinesses.Where(k => k.UserName == Request.Cookies["UserId"].Value);
                    if (query != null)
                    {
                        EntityBusiness p = query.First();
                        string fileToDelete = Path.Combine(Server.MapPath("~/Content/Images/Avatar"), p.Avatar); // file hinh cu
                        System.IO.File.Delete(fileToDelete); 
                        p.Avatar = encodestring;
                        db.SubmitChanges();

                      
                        Response.Cookies["UserAvatar"].Value = encodestring;
                    }
                  
                    
                }

                return RedirectToAction("InfoUser", "Organization");
            }
            catch
            {
               
                return RedirectToAction("InfoUser", "Organization");
            }
        }
        #endregion
        #region "Thống kê"
        public ActionResult Statistic()
        {
            dbVideoDataContext db = new dbVideoDataContext();
            ViewBag.CompanyName = db.EntityBusinesses.Where(g => g.UserName == Request.Cookies["UserId"].Value).First().CompanyName;
            var query = db.EntityVideos.Where(g => g.IdBusiness == Request.Cookies["UserId"].Value).OrderBy(g=>g.Status); 
            return View(query.ToList());
        }
        [HttpPost]
        public JsonResult saveExcel(int IdVideo)
        {
            try
            {
                dbVideoDataContext db = new dbVideoDataContext();
                EntityVideo video = db.EntityVideos.Where(g => g.Id == IdVideo).First();
                Workbook book = new Workbook();
                Worksheet sheet = book.Worksheets.Add("test");
                //1> Dau tien phai khoi tao toan bo o excel
                //khoi tao so luong cot la 50
                for (int i = 0; i < 20; i++)
                {
                    WorksheetColumn col = new WorksheetColumn(150);//chieu rong
                    sheet.Table.Columns.Add(col);//add vao 50 cot
                }

                //khoi tao so luong dong la 50, moi dong co 50 cell

                int sldong = db.EntityListQuestionMutiOfUsers.Where(g => g.EntityQuestions_MultipleChoice.IdVideo == video.Id).ToList().Count();
                int slchia = db.EntityQuestions_MultipleChoices.Where(g => g.IdVideo == video.Id && g.Type == 2).ToList().Count();
                if (video.TypeQuestion != 2)
                {
                    sldong = db.EntityListQuestionOfUsers.Where(g => g.EntityQuestions_Essay.IdVideo == video.Id).ToList().Count();
                    slchia = db.EntityQuestions_Essays.Where(g => g.IdVideo == video.Id).ToList().Count();
                }
                sldong = (sldong / slchia) + 20;
                for (int i = 0; i < sldong; i++)
                {
                    WorksheetRow Row = new WorksheetRow();
                    sheet.Table.Rows.Add(Row);
                    for (int j = 0; j < 20; j++)
                    {
                        WorksheetCell Cell = new WorksheetCell();
                        sheet.Table.Rows[i].Cells.Add(Cell);
                    }
                }

                // Tạo định dạng font
                WorksheetStyle style = book.Styles.Add("TieuDe");
                style.Font.FontName = "Arial";
                style.Font.Size = 15;
                style.Interior.Color = "#009966";   // dong nay to background
                style.Interior.Pattern = StyleInteriorPattern.Solid; // dong nay to background
                style.Font.Bold = true;
                style.Alignment.Horizontal = StyleHorizontalAlignment.Center;
                style.Font.Color = "White";

                WorksheetStyle style2 = book.Styles.Add("TenCongTy");
                style2.Font.FontName = "Arial";
                style2.Font.Size = 9;
                style2.Font.Bold = true;
                style2.Alignment.Horizontal = StyleHorizontalAlignment.Right;
                style2.Font.Color = "#009966";

                
                WorksheetStyle style3 = book.Styles.Add("Left");
                style3.Font.FontName = "Arial";
                style3.Font.Size = 10;
                //style3.Interior.Color = "#0033FF";   // dong nay to background
                //style3.Interior.Pattern = StyleInteriorPattern.Solid; // dong nay to background
                style3.Font.Bold = true;
                style3.Alignment.Horizontal = StyleHorizontalAlignment.Left;
                style3.Font.Color = "#0033FF";

            
               // style5.Alignment.Horizontal = StyleHorizontalAlignment.Center;


                WorksheetCell inputCell = new WorksheetCell();

                inputCell = new WorksheetCell();
                inputCell.MergeAcross = 3;
                inputCell.Data.Type = DataType.String;
                inputCell.Data.Text = "BÁO CÁO VIDEO" + video.Name.ToString().ToUpper();
                inputCell.StyleID = "TieuDe";
                sheet.Table.Rows[1].Cells[1] = inputCell;

                inputCell = new WorksheetCell();
                inputCell.MergeAcross = 3;
                inputCell.Data.Type = DataType.String;
                inputCell.Data.Text = "Doanh nghiệp: " + video.EntityBusiness.CompanyName.ToString();
                inputCell.StyleID = "TenCongTy";
                sheet.Table.Rows[2].Cells[1] = inputCell;

                inputCell = new WorksheetCell();
                inputCell.Data.Type = DataType.String;
                inputCell.Data.Text = "Link Video";
                inputCell.StyleID = "Left";
                sheet.Table.Rows[4].Cells[0] = inputCell;

                inputCell = new WorksheetCell();
                inputCell.Data.Type = DataType.String;
                inputCell.Data.Text = "http://www.youtube.com/watch?v=" + video.Video.ToString();
                sheet.Table.Rows[4].Cells[1] = inputCell;

                inputCell = new WorksheetCell();
                inputCell.Data.Type = DataType.String;
                inputCell.Data.Text = "Lượt xem";
                inputCell.StyleID = "Left";
                sheet.Table.Rows[5].Cells[0] = inputCell;

                inputCell = new WorksheetCell();
                inputCell.Data.Type = DataType.String;
                inputCell.Data.Text = video.Views.ToString();
                sheet.Table.Rows[5].Cells[1] = inputCell;

                inputCell = new WorksheetCell();
                inputCell.Data.Type = DataType.String;
                inputCell.Data.Text = "Lượt đã xem";
                inputCell.StyleID = "Left";
                sheet.Table.Rows[6].Cells[0] = inputCell;

                inputCell = new WorksheetCell();
                inputCell.Data.Type = DataType.String;
                inputCell.Data.Text = video.Viewed.ToString();
                sheet.Table.Rows[6].Cells[1] = inputCell;

                inputCell = new WorksheetCell();
                inputCell.Data.Type = DataType.String;
                inputCell.Data.Text = "Tình trạng";
                inputCell.StyleID = "Left";
                sheet.Table.Rows[7].Cells[0] = inputCell;

                inputCell = new WorksheetCell();
                inputCell.Data.Type = DataType.String;

                if (video.Status == 1)
                    inputCell.Data.Text = "Đang hoạt động";
                else if (video.Status == 2)
                {
                    inputCell.Data.Text = "Đang bị tạm ngưng";
                }
                else
                    inputCell.Data.Text = "Đã hết lượt view";

                sheet.Table.Rows[7].Cells[1] = inputCell;


                inputCell = new WorksheetCell();
                inputCell.Data.Type = DataType.String;
                inputCell.Data.Text = "Tiêu chí người xem";
                inputCell.StyleID = "Left";
                sheet.Table.Rows[8].Cells[0] = inputCell;

                inputCell = new WorksheetCell();
                inputCell.Data.Type = DataType.String;
                inputCell.Data.Text = video.Criteria;
                sheet.Table.Rows[8].Cells[1] = inputCell;

                inputCell = new WorksheetCell();
                inputCell.Data.Type = DataType.String;
                inputCell.Data.Text = "Ngày hết của video gần đây";
                inputCell.StyleID = "Left";
                sheet.Table.Rows[9].Cells[0] = inputCell;

                inputCell = new WorksheetCell();
                inputCell.Data.Type = DataType.String;
                inputCell.Data.Text = video.Viewed_Date.ToString();
                sheet.Table.Rows[9].Cells[1] = inputCell;

                // phần thống kê
                // style
                WorksheetStyle style4 = book.Styles.Add("ThongKeTN");
                style4.Font.FontName = "Arial";
                style4.Interior.Color = "#FF9900";   // dong nay to background
                style4.Interior.Pattern = StyleInteriorPattern.Solid; // dong nay to background
                style4.Font.Bold = true;
                style4.Alignment.Horizontal = StyleHorizontalAlignment.Center;

                WorksheetStyle style5 = book.Styles.Add("ThongKeTNChiTiet");
                style5.Font.FontName = "Arial";
                style5.Interior.Color = "#99CCFF";   // dong nay to background
                style5.Interior.Pattern = StyleInteriorPattern.Solid; // dong nay to background

                // thanh nội dung
                inputCell = new WorksheetCell();
                inputCell.MergeAcross = 1;
                inputCell.Data.Type = DataType.String;
                inputCell.Data.Text = "THỐNG KÊ";
                inputCell.StyleID = "ThongKeTN";
                sheet.Table.Rows[12].Cells[0] = inputCell;

                inputCell = new WorksheetCell();
                inputCell.Data.Type = DataType.String;
                inputCell.Data.Text = "UserName";
                inputCell.StyleID = "ThongKeTNChiTiet";
                sheet.Table.Rows[16].Cells[0] = inputCell;

                inputCell = new WorksheetCell();
                inputCell.Data.Type = DataType.String;
                inputCell.Data.Text = "Nơi cư trú";
                inputCell.StyleID = "ThongKeTNChiTiet";
                sheet.Table.Rows[16].Cells[1] = inputCell;

                inputCell = new WorksheetCell();
                inputCell.Data.Type = DataType.String;
                inputCell.Data.Text = "Độ tuổi";
                inputCell.StyleID = "ThongKeTNChiTiet";
                sheet.Table.Rows[16].Cells[2] = inputCell;

                inputCell = new WorksheetCell();
                inputCell.Data.Type = DataType.String;
                inputCell.Data.Text = "Giới tính";
                inputCell.StyleID = "ThongKeTNChiTiet";
                sheet.Table.Rows[16].Cells[3] = inputCell;

                if (video.TypeQuestion == 2)
                {
                    IEnumerable<EntityQuestions_MultipleChoice> cp = db.EntityQuestions_MultipleChoices.Where(g => g.IdVideo == IdVideo && g.Type == 2).ToList();
                    int ii = 4;
                    int j = 17;

                    inputCell = new WorksheetCell();
                    inputCell.Data.Type = DataType.String;
                    inputCell.Data.Text = "Đáp án a";
                    inputCell.StyleID = "ThongKeTNChiTiet";
                    sheet.Table.Rows[3].Cells[7] = inputCell;

                    inputCell = new WorksheetCell();
                    inputCell.Data.Type = DataType.String;
                    inputCell.Data.Text = "Đáp án b";
                    inputCell.StyleID = "ThongKeTNChiTiet";
                    sheet.Table.Rows[3].Cells[8] = inputCell;

                    inputCell = new WorksheetCell();
                    inputCell.Data.Type = DataType.String;
                    inputCell.Data.Text = "Đáp án c";
                    inputCell.StyleID = "ThongKeTNChiTiet";
                    sheet.Table.Rows[3].Cells[9] = inputCell;

                    inputCell = new WorksheetCell();
                    inputCell.Data.Type = DataType.String;
                    inputCell.Data.Text = "Đáp án d";
                    inputCell.StyleID = "ThongKeTNChiTiet";
                    sheet.Table.Rows[3].Cells[10] = inputCell;

                    foreach (var item in cp)
                    {
                        
                            inputCell = new WorksheetCell();
                            inputCell.Data.Type = DataType.String;
                            inputCell.Data.Text = "Câu " + (ii - 3).ToString();
                            inputCell.StyleID = "ThongKeTNChiTiet";
                            sheet.Table.Rows[16].Cells[ii] = inputCell;
                       
                        inputCell = new WorksheetCell();
                        inputCell.Data.Type = DataType.String;
                        inputCell.Data.Text = "Câu " + (ii - 3).ToString();
                        inputCell.StyleID = "ThongKeTNChiTiet";
                        sheet.Table.Rows[ii].Cells[4] = inputCell;

                      

                        inputCell = new WorksheetCell();
                        inputCell.MergeAcross = 1;
                        inputCell.Data.Type = DataType.String;
                        inputCell.Data.Text = item.Des.ToString();
                        sheet.Table.Rows[ii].Cells[5] = inputCell;

                        inputCell = new WorksheetCell();
                        inputCell.Data.Type = DataType.String;
                        inputCell.Data.Text = item.QuesA;
                        sheet.Table.Rows[ii].Cells[6] = inputCell;

                        inputCell = new WorksheetCell();
                        inputCell.Data.Type = DataType.String;
                        inputCell.Data.Text = item.QuesB;
                        sheet.Table.Rows[ii].Cells[7] = inputCell;

                        inputCell = new WorksheetCell();
                        inputCell.Data.Type = DataType.String;
                        inputCell.Data.Text = item.QuesC;
                        sheet.Table.Rows[ii].Cells[8] = inputCell;

                        inputCell = new WorksheetCell();
                        inputCell.Data.Type = DataType.String;
                        inputCell.Data.Text = item.QuesD;
                        sheet.Table.Rows[ii].Cells[9] = inputCell;
                       
                        ii++;
                    }
                    var list = db.EntityListQuestionMutiOfUsers
                        .Where(g => g.EntityQuestions_MultipleChoice.EntityVideo.Id == video.Id)
                        .GroupBy(h => new { h.Date,h.UserName })
                        .Select(u => u.First()).ToList();
                    foreach (var it in list)
                    {

                        inputCell = new WorksheetCell();
                        inputCell.Data.Type = DataType.String;
                        inputCell.Data.Text = it.UserName;
                        sheet.Table.Rows[j].Cells[0] = inputCell;

                        inputCell = new WorksheetCell();
                        inputCell.Data.Type = DataType.String;
                        inputCell.Data.Text = it.EntityUser.City;
                        sheet.Table.Rows[j].Cells[1] = inputCell;

                        inputCell = new WorksheetCell();
                        inputCell.Data.Type = DataType.String;
                        inputCell.Data.Text = (DateTime.Now.Year - it.EntityUser.YearBorn).ToString();
                        sheet.Table.Rows[j].Cells[2] = inputCell;

                        inputCell = new WorksheetCell();
                        inputCell.Data.Type = DataType.String;
                        inputCell.Data.Text = it.EntityUser.Sex;
                        sheet.Table.Rows[j].Cells[3] = inputCell;

                        var query = db.EntityListQuestionMutiOfUsers.Where(g => g.UserName == it.UserName && g.Date == it.Date && g.EntityQuestions_MultipleChoice.IdVideo == video.Id).ToList();
                        int cot = 3;
                        foreach (var qu in query)
                        {
                            
                            inputCell = new WorksheetCell();
                            inputCell.Data.Type = DataType.String;
                            inputCell.Data.Text = qu.Result;
                            sheet.Table.Rows[j].Cells[cot+ 1] = inputCell;

                            cot++;
                        }
                        inputCell = new WorksheetCell();
                        inputCell.Data.Type = DataType.String;
                        inputCell.Data.Text = query.ToList().First().Date.ToString();
                        sheet.Table.Rows[j].Cells[cot+ 1] = inputCell;
                        j++;
                    }

                }
                else
                {
                    IEnumerable<EntityQuestions_Essay> cp = db.EntityQuestions_Essays.Where(g => g.IdVideo == IdVideo).ToList();
                    int ii = 4;
                    int j = 17;
                    foreach (var item in cp)
                    {

                        inputCell = new WorksheetCell();
                        inputCell.Data.Type = DataType.String;
                        inputCell.Data.Text = "Câu " + (ii - 3).ToString();
                        inputCell.StyleID = "ThongKeTNChiTiet";
                        sheet.Table.Rows[16].Cells[ii] = inputCell;

                        inputCell = new WorksheetCell();
                        inputCell.Data.Type = DataType.String;
                        inputCell.Data.Text = "Câu " + (ii - 3).ToString();
                        inputCell.StyleID = "ThongKeTNChiTiet";
                        sheet.Table.Rows[ii].Cells[4] = inputCell;

                        inputCell = new WorksheetCell();
                        inputCell.MergeAcross = 1;
                        inputCell.Data.Type = DataType.String;
                        inputCell.Data.Text = item.Des.ToString();
                        sheet.Table.Rows[ii].Cells[5] = inputCell;
                        ii++;
                    }
                    var list = db.EntityListQuestionOfUsers
                        .Where(g => g.EntityQuestions_Essay.EntityVideo.Id == video.Id)
                        .GroupBy(h => new { h.Date, h.UserName })
                        .Select(u => u.First()).ToList();
                    foreach (var it in list)
                    {

                        inputCell = new WorksheetCell();
                        inputCell.Data.Type = DataType.String;
                        inputCell.Data.Text = it.UserName;
                        sheet.Table.Rows[j].Cells[0] = inputCell;

                        inputCell = new WorksheetCell();
                        inputCell.Data.Type = DataType.String;
                        inputCell.Data.Text = it.EntityUser.City;
                        sheet.Table.Rows[j].Cells[1] = inputCell;

                        inputCell = new WorksheetCell();
                        inputCell.Data.Type = DataType.String;
                        inputCell.Data.Text = (DateTime.Now.Year - it.EntityUser.YearBorn).ToString();
                        sheet.Table.Rows[j].Cells[2] = inputCell;

                        inputCell = new WorksheetCell();
                        inputCell.Data.Type = DataType.String;
                        inputCell.Data.Text = it.EntityUser.Sex;
                        sheet.Table.Rows[j].Cells[3] = inputCell;

                        var query = db.EntityListQuestionOfUsers.Where(g => g.UserName == it.UserName && g.Date == it.Date && g.EntityQuestions_Essay.IdVideo == video.Id).ToList();
                        int cot = 3;
                        foreach (var qu in query)
                        {

                            inputCell = new WorksheetCell();
                            inputCell.Data.Type = DataType.String;
                            inputCell.Data.Text = qu.Result;
                            sheet.Table.Rows[j].Cells[cot + 1] = inputCell;

                            cot++;
                        }
                        inputCell = new WorksheetCell();
                        inputCell.Data.Type = DataType.String;
                        inputCell.Data.Text = query.ToList().First().Date.ToString();
                        sheet.Table.Rows[j].Cells[cot + 1] = inputCell;

                        j++;
                    }

                }
                string name = video.Name + ".xls";
                var path = Path.Combine(Server.MapPath("~/Content/"), name);
                book.Save(path);

                return Json(name);
            }
            catch
            {
                return Json("Error");
            }
        }

        #endregion

        #region "Gia hạn hợp đồng"
        public ActionResult Contract_Renewals()
        {
            dbVideoDataContext db = new dbVideoDataContext();
            ViewBag.NoiDung = db.EntityThongBaos.Where(g=>g.Id == 2).First().NoiDung;
            var query = db.EntityVideos.Where(g => g.IdBusiness == Request.Cookies["UserId"].Value).OrderBy(g => g.Status); 
            return View(query.ToList());
        }
      
        #endregion

    }
}
